CREATE DATABASE KillBillDB
GO

USE KillBillDB
GO


Create table [Person]
(
	[PersonID] Char(10) NOT NULL,
	[RUC] Char(13) NULL,
	[FirstName] Varchar(20) NOT NULL,
	[LastName] Varchar(20) NOT NULL,
	[Email] Varchar(40) NOT NULL,
	[Address] Nvarchar(100) NULL,
	[Phone] Nvarchar(10) NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([PersonID])
) 
go

Create table [Customer]
(
	[CustomerID] Char(10) NOT NULL,
	[CustomerRUC] Char(13) NOT NULL,
	[Reference] Varchar(200) NULL,
	[IsActive] Bit NOT NULL,
Primary Key ([CustomerID])
) 
go

Create table [Employee]
(
	[EmployeeID] Char(10) NOT NULL,
	[Password] Nvarchar(128) NOT NULL,
	[IsActive] Bit NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([EmployeeID])
) 
go

Create table [EmployeeRole]
(
	[EmployeeRoleID] Varchar(40) NOT NULL,
	[Description] Nvarchar(100) NOT NULL,
Primary Key ([EmployeeRoleID])
) 
go

Create table [EmployeeActivity]
(
	[EmployeeRoleID] Varchar(40) NOT NULL,
	[EmployeeID] Char(10) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([EmployeeRoleID],[EmployeeID])
) 
go

Create table [Provider]
(
	[ProviderID] Integer Identity NOT NULL,
	[ContactID] Char(10) NOT NULL,
	[ProviderRUC] Nvarchar(13) NOT NULL,
	[Name] Varchar(20) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
	[IsActive] Bit NOT NULL,
Primary Key ([ProviderID])
) 
go

Create table [ProviderOffice]
(
	[ProviderID] Integer NOT NULL,
	[Address] Varchar(200) NOT NULL,
	[Phone] Char(10) NOT NULL,
	[IsMain] Bit NOT NULL,
Primary Key ([ProviderID],[Address])
) 
go

Create table [PurchaseHeader]
(
	[PurchaseHeaderID] Integer Identity NOT NULL,
	[ProviderID] Integer NOT NULL,
	[SubTotal] Money NOT NULL,
	[TaxAmount] Decimal(10,2) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([PurchaseHeaderID])
) 
go

Create table [Product]
(
	[ProductID] Integer NOT NULL,
	[ProviderID] Integer NOT NULL,
	[Name] Varchar(40) NOT NULL, UNIQUE ([Name]),
	[Description] Nvarchar(100) NOT NULL,
	[LastPrice] Money NOT NULL,
	[StockMin] Integer NOT NULL,
	[StockMax] Integer NOT NULL,
	[IVAApplied] Bit NOT NULL,
	[ICETax] Float NOT NULL,
	[IsActive] Bit NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([ProductID])
) 
go

Create table [PurchaseDetail]
(
	[PurchaseHeaderID] Integer NOT NULL,
	[ProductID] Integer NOT NULL,
	[OrderDate] Datetime NOT NULL,
	[UnitPrice] Money NOT NULL,
	[OrderQty] Integer NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([PurchaseHeaderID],[ProductID])
) 
go

Create table [SalesHeader]
(
	[SalesHeaderID] Integer NOT NULL,
	[EmployeeID] Char(10) NOT NULL,
	[CustomerID] Char(10) NOT NULL,
	[SubTotal] Money NOT NULL,
	[TaxAmount] Decimal(10,2) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([SalesHeaderID])
) 
go

Create table [SalesDetail]
(
	[SalesHeaderID] Integer NOT NULL,
	[ProductID] Integer NOT NULL,
	[OrderDate] Datetime NOT NULL,
	[UnitPrice] Money NOT NULL,
	[OrderQty] Integer NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([SalesHeaderID],[ProductID])
) 
go

Create table [Parameter]
(
	[ParameterID] Nvarchar(20) NOT NULL,
	[Name] Nvarchar(20) NOT NULL,
	[Value] Nvarchar(20) NOT NULL,
Primary Key ([ParameterID])
) 
go

Alter table [Employee] add  foreign key([EmployeeID]) references [Person] ([PersonID])  on update no action on delete no action 
go
Alter table [Customer] add  foreign key([CustomerID]) references [Person] ([PersonID])  on update no action on delete no action 
go
Alter table [Provider] add  foreign key([ContactID]) references [Person] ([PersonID])  on update no action on delete no action 
go
Alter table [SalesHeader] add  foreign key([CustomerID]) references [Customer] ([CustomerID])  on update cascade on delete cascade 
go
Alter table [EmployeeActivity] add  foreign key([EmployeeID]) references [Employee] ([EmployeeID])  on update no action on delete no action 
go
Alter table [SalesHeader] add  foreign key([EmployeeID]) references [Employee] ([EmployeeID])  on update cascade on delete cascade 
go
Alter table [EmployeeActivity] add  foreign key([EmployeeRoleID]) references [EmployeeRole] ([EmployeeRoleID])  on update cascade on delete no action 
go
Alter table [ProviderOffice] add  foreign key([ProviderID]) references [Provider] ([ProviderID])  on update cascade on delete cascade 
go
Alter table [PurchaseHeader] add  foreign key([ProviderID]) references [Provider] ([ProviderID])  on update cascade on delete cascade 
go
Alter table [Product] add  foreign key([ProviderID]) references [Provider] ([ProviderID])  on update no action on delete no action 
go
Alter table [PurchaseDetail] add  foreign key([PurchaseHeaderID]) references [PurchaseHeader] ([PurchaseHeaderID])  on update cascade on delete cascade 
go
Alter table [PurchaseDetail] add  foreign key([ProductID]) references [Product] ([ProductID])  on update cascade on delete no action 
go
Alter table [SalesDetail] add  foreign key([ProductID]) references [Product] ([ProductID])  on update cascade on delete no action 
go
Alter table [SalesDetail] add  foreign key([SalesHeaderID]) references [SalesHeader] ([SalesHeaderID])  on update cascade on delete cascade 
go


Set quoted_identifier on
go


Set quoted_identifier off
go


